package hnx.ca.db.dao;

import hnx.ca.db.object.AccountCA;
import hnx.ca.db.pool.DBConfig;
import hnx.ca.db.pool.DBPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TblAccountCADAO {

	
	/**
	 * @Todo to get accountCA from customerId 
	 * @author sonph
	 * @date 20 Apr 2014
	 * 
	 * 
	 * @param customerId
	 * @return
	 * 
	 */
	public static AccountCA getAccountCA(String customerId){
		AccountCA accountCA = new AccountCA();
		Connection conn = null;
		PreparedStatement pstmt = null;
		StringBuilder query = new StringBuilder("");
		DBConfig dbConfig = new DBConfig();
		try{
			conn = getConnection();
			String schema = dbConfig.getDb_schema();
			/*
			 * setting querry
			 */
			query.append("SELECT a.* ");
			query.append("FROM " + schema + "TBL_ACCOUNT_CA a ");
			query.append("WHERE a.CUSTOMER_ID = '" + customerId + "'");
			pstmt = conn.prepareStatement(query.toString());
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				int accountCaId = rs.getInt("ACCOUNT_CA_ID");
				String subject = rs.getString("SUBJECT");
				String serialNumber = rs.getString("SERIAL_NUMBER");
				String validFrom = rs.getString("VALID_FROM");
				String validTo = rs.getString("VALID_TO");
				String publicKey = rs.getString("PUBLIC_KEY");
				int accountCaStatus = rs.getInt("ACCOUNTCA_STATUS");
				int customerStatus = rs.getInt("CUSTOMER_STATUS");
				int partnerId = rs.getInt("PARTNER_ID");
				String accountCaIssuer = rs.getString("ACCOUNT_CA_ISSUER");
				accountCA = new AccountCA(accountCaId, subject, serialNumber, validFrom, validTo, publicKey, accountCaStatus, customerStatus, partnerId, customerId, accountCaIssuer);
			}
		}catch(Exception e){
			e.printStackTrace();
			accountCA = null;
		}
		return accountCA;
	}

	
	/**
	 * @Todo to get accountCA from customerId 
	 * @author sonph
	 * @date 20 Apr 2014
	 * 
	 * 
	 * @param customerId
	 * @return
	 * 
	 */
	public static int getPartnerStatus(int partnerId){
		Connection conn = null;
		PreparedStatement pstmt = null;
		StringBuilder query = new StringBuilder("");
		DBConfig dbConfig = new DBConfig();
		int status = -1;
		try{
			conn = getConnection();
			String schema = dbConfig.getDb_schema();
			/*
			 * setting querry
			 */
			query.append("SELECT a.* ");
			query.append("FROM " + schema + "TBL_PARTNER a ");
			query.append("WHERE a.PARTNER_ID = " + partnerId);
			pstmt = conn.prepareStatement(query.toString());
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				status = rs.getInt("PARTNER_STATUS");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return status;
	}
	
	public static Connection getConnection() throws SQLException {
		return DBPool.getConnection();
	}
	public static void releaseConnection(Connection conn, PreparedStatement preStmt) {
		DBPool.releaseConnection(conn, preStmt);
	}
	
	public static void main(String args[]){
		AccountCA a = getAccountCA("CIMS0003342");
		System.out.println(a.getAccountCaId());
		System.out.println(a.getAccountCaIssuer());
		//System.out.println(a.getSubject());
		System.out.println(a.getPublicKey());
	}

}

